/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package util;

import java.util.List;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Vector;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 数据库通用类
 *
 * @author SoChishun
 * @version 1.0 2016-5-17
 * @see http://www.verysource.com/code/2817397_1/dbutil.java.html
 */
public class DbUtil {

    private static Log log = LogFactory.getLog(DbUtil.class);

    /**
     * 数据库连接信息
     */
    private static ConnectionInfo conninfo = null;
    /**
     * 数据库连接地址
     */
    private static String url = null;

    public static void setConnInfo(ConnectionInfo info) throws SQLException {
        conninfo = info;
        // jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
        // jdbc:jtds:sqlserver://127.0.0.1:1433;DatabaseName=ebuilderNew;SelectMethod=cursor;charset=GBK
        // jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
        url = "jdbc:mysql://" + conninfo.host + ":" + conninfo.port + "/" + conninfo.database;
        String urlparam = "";
        if (null != conninfo.useSSL) {
            urlparam += "&useSSL=" + conninfo.useSSL;
        }
        if (null != conninfo.charset && conninfo.charset.length() > 0) {
            urlparam += "&useUnicode=true&characterEncoding=" + conninfo.charset;
        }
        if (urlparam.length() > 0) {
            url += '?' + urlparam.substring(1);
        }
        try (Connection connection = getConn()) {
        }
    }

    /**
     * 获取数据库连接，失败返回null。
     *
     * @param batchQuery 是否优化批量操作
     * @return 返回一个数据库连接，失败返回null空对象。
     * @see http://www.verysource.com/code/2817396_1/dbconnection.java.html
     */
    public static Connection getConn(boolean batchQuery) {
        if (null == url || null == conninfo.driver) {
            return null;
        }
        if (batchQuery) {
            url = url + "&rewriteBatchedStatements=true";
        }
        Connection conn = null;
        try {
            // com.mysql.jdbc.Driver
            // net.sourceforge.jtds.jdbc.Driver
            Class.forName(conninfo.driver);
            conn = DriverManager.getConnection(url, conninfo.user, conninfo.password);
        } catch (ClassNotFoundException e) {
            log.fatal("加载驱动程序" + conninfo.driver + "出错！请检查是否存在相应数据库的驱动程序。", e);
        } catch (SQLException sqle) {
            log.fatal("获取数据库连接的时候出现错误！", sqle);
        }
        return conn;
    }

    /**
     * 获取数据库连接，失败返回null。
     *
     * @return
     */
    public static Connection getConn() {
        return getConn(false);
    }

    /**
     * 如果执行成功，那么就返回影响的记录数目。否则返回0；<br />
     * 注意：如果是多条sql语句用分号“;”分割，那么它返回的影响记录的数目就不准了。所以只用来执行单条sql语句。<br>
     * sqlserver2000返回的是第一条sql语句影响的记录数。<br>
     *
     * @param sql 不能为空，可以是insert、update、delete语句；
     * @return int
     */
    public static int executeUpdate(String sql) {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空，必须是insert、update或者delete语句。");
            return 0;
        }
        int result = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConn();
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);
            result = pstmt.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.info("数据库会滚失败！", e);
            }
            log.error(sql + "语句执行失败！请仔细检查！", e);
            result = 0;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                log.info("关闭PreparedStatement失败！", e);
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.info("关闭数据库连接失败！", e);
            }
        }
        return result;
    }

    /**
     * 可以用来执行任意的sql语句。
     *
     * @param sql
     * @return 返回true if the first result is a ResultSet object; false if the first result is an update count or there is no result
     *
     * @throws SQLException 抛出异常说明执行失败！否则执行成功！
     */
    private static void execute(String sql) throws SQLException {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空。");
            return;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConn();
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);
            pstmt.execute();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                log.info("数据库会滚失败！", e);
            }
            log.error(sql + "语句执行失败！请仔细检查！", e);
            throw new SQLException("执行失败！");
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException e) {
                log.info("关闭PreparedStatement失败！", e);
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.info("关闭数据库连接失败！", e);
            }
        }
    }

    /**
     * 关闭ResultSet对象
     *
     * @param rs
     */
    public static void closeResultSet(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            log.error("关闭ResultSet出错！", e);
        }
    }

    /**
     * 关闭Statement对象。
     *
     * @param stmt
     */
    public static void closeStatement(Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            log.error("关闭Statement出错！", e);
        }
    }

    /**
     * 关闭PreparedStatement对象
     *
     * @param pstmt
     */
    public static void closeStatement(PreparedStatement pstmt) {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
            log.error("关闭PreparedStatement出错！", e);
        }
    }

    /**
     * 关闭Connection
     *
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            log.error("关闭数据库连接出错！", e);
        }
    }

    /**
     * 同时关闭Statement和Connection
     *
     * @param stmt
     * @param conn
     */
    public static void closeStatementAndConnection(Statement stmt,
            Connection conn) {
        closeStatement(stmt);
        closeConnection(conn);
    }

    /**
     * 同时关闭PreparedStatement和Connection
     *
     * @param pstmt
     * @param conn
     */
    public static void closeStatementAndConnection(PreparedStatement pstmt,
            Connection conn) {
        closeStatement(pstmt);
        closeConnection(conn);
    }

    /**
     * 同时关闭ResultSet、 Statement 、Connection
     *
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
        closeResultSet(rs);
        closeStatement(stmt);
        closeConnection(conn);
    }

    /**
     * 同时关闭ResultSet、 PreparedStatement 、Connection
     *
     * @param rs
     * @param pstmt
     * @param conn
     */
    public static void closeAll(ResultSet rs, PreparedStatement pstmt,
            Connection conn) {
        closeResultSet(rs);
        closeStatement(pstmt);
        closeConnection(conn);
    }

    /**
     * 返回结果集Vector。线程安全的。Vector中包含的是HashTable。HashTable的key就sql语句中的字段名的小写。
     *
     * @param sql
     * @return Vector,返回的结果要么为null，要么至少包含一条查询记录；
     * @throws SQLException
     */
    public static Vector executeQueryVector(String sql) {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空，必须是select语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Vector v = new Vector();
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            ResultSetMetaData rsmd = pstmt.getMetaData();
            int columnCount = rsmd.getColumnCount();//获取所有的列数；
            String[] columnStr = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                columnStr[i - 1] = rsmd.getColumnName(i).toLowerCase();//都转换为小写
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Hashtable hash = new Hashtable();
                for (int i = 0; i < columnCount; i++) {
                    hash.put(columnStr[i], rs.getObject(columnStr[i]) == null ? "" : rs.getObject(columnStr[i])); //如果key和value任意一个为null，均会抛出异常
                }
                v.add(hash);
            }
            if (v.size() < 1) {
                v = null;
            }
        } catch (SQLException e) {
            log.error(sql + "语句查询出错！", e);
            return null;
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return v;
    }

    /**
     * 返回结果集List。 list中包含的是hashmap。hashMap的key就sql语句中的字段名的小写。
     *
     * @param sql
     * @return list,返回的结果list要么为null，要么包含数据；
     * @throws SQLException
     */
    public static List executeQueryList(String sql) {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空，必须是select语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List l = new ArrayList();
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            ResultSetMetaData rsmd = pstmt.getMetaData();
            int columnCount = rsmd.getColumnCount();//获取所有的列数；
            String[] columnStr = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                columnStr[i - 1] = rsmd.getColumnName(i).toLowerCase();//都转换为小写
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                HashMap map = new HashMap();
                for (int i = 0; i < columnCount; i++) {
                    map.put(columnStr[i], rs.getObject(columnStr[i]) == null ? "" : rs.getObject(columnStr[i])); //如果key和value任意一个为null，均会抛出异常
                }
                l.add(map);
            }
            if (l.size() < 1) {
                l = null;
            }
        } catch (SQLException e) {
            log.error(sql + "语句查询出错！", e);
            return null;
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return l;
    }

    /**
     * 返回结果集List。 list中包含的是String[]数组。 她不像List中包含HashMap，直接保存的就是结果没有key。
     *
     * @param sql
     * @return list,返回的结果list要么为null，要么包含数据；
     * @throws SQLException
     */
    public static List executeQueryStringList(String sql) {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空，必须是select语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List l = new ArrayList();
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            ResultSetMetaData rsmd = pstmt.getMetaData();
            int columnCount = rsmd.getColumnCount();//获取所有的列数；
            String[] columnStr = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                columnStr[i - 1] = rsmd.getColumnName(i).toLowerCase();//都转换为小写
            }
            rs = pstmt.executeQuery();
//Object[] tempObject = null;
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String[] tempObject = new String[columnCount];
                for (int i = 0; i < columnCount; i++) {
                    tempObject[i] = rs.getObject(columnStr[i]) == null ? "" : rs.getObject(columnStr[i]).toString(); //如果key和value任意一个为null，均会抛出异常
                }
                l.add(tempObject);
            }
            if (l.size() < 1) {
                l = null;
            }
        } catch (SQLException e) {
            log.error(sql + "语句查询出错！", e);
            return null;
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return l;
    }

    /**
     * 分页查询
     *
     * @param pagingInfo
     * @param tableName
     * @param fields
     * @param where
     * @param order
     * @return
     * @version 1.0 2016-5-18 SoChishun Added.
     */
    public static List executePagingList(DbPagingInfo pagingInfo, String tableName, String fields, String where, String order) {
        if (tableName == null || tableName.trim().length() < 1) {
            log.info("参数sql不能为空，必须是select语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List l = new ArrayList();
        try {
            if (null != order && where.length() > 0) {
                where = " where " + where;
            }
            if (null != order && order.length() > 0) {
                order = " order by " + order;
            }
            // 查询总记录数
            String sqlcount = String.format("select count(*) from %s %s", tableName, where);
            conn = getConn();
            pstmt = conn.prepareStatement(sqlcount);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                pagingInfo.recordCount = rs.getInt(0);
            }
            // 分页查询
            int offset = (pagingInfo.pageID - 1) * pagingInfo.pageSize;
            if (offset > pagingInfo.recordCount) {
                offset = pagingInfo.recordCount;
            }
            String limit = "limit " + offset + "," + pagingInfo.pageSize;
            String sqlquery = String.format("select %s from %s %s %s %s", fields, tableName, where, order, limit);
            ResultSetMetaData rsmd = pstmt.getMetaData();
            int columnCount = rsmd.getColumnCount();//获取所有的列数；
            String[] columnStr = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                columnStr[i - 1] = rsmd.getColumnName(i).toLowerCase();//都转换为小写
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                HashMap map = new HashMap();
                for (int i = 0; i < columnCount; i++) {
                    map.put(columnStr[i], rs.getObject(columnStr[i]) == null ? "" : rs.getObject(columnStr[i])); //如果key和value任意一个为null，均会抛出异常
                }
                l.add(map);
            }
            if (l.size() < 1) {
                l = null;
            }
        } catch (SQLException e) {
            log.error("语句查询出错！", e);
            return null;
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return l;
    }

    /**
     * 启用事务批量执行Sql并返回受影响的行数
     *
     * @param sql
     * @return
     * @version 1.0 2016-5-17 SoChishun Added.
     * @see http://www.bubuko.com/infodetail-838146.html
     */
    public static int executeBatch(List<String> sqls) {
        if (sqls.size() < 1) {
            log.info("参数sql不能为空，必须是insert或update语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        int count = -1;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int i = 0;
        try {
            conn = getConn(true);
            conn.setAutoCommit(false);//将自动提交关闭
            stmt = conn.createStatement();
            for (String sql : sqls) {
                stmt.addBatch(sql);
                if (i > 0 && i % 500 == 0) { //每500条执行一次，避免内存不够的情况
                    stmt.executeBatch();
                    //如果不想出错后，完全没保留数据，则可以没执行一次提交一次，但得保证数据不会重复
                    conn.commit();
                }
                i++;
            }
            stmt.executeBatch(); // 其中整形常量的值分别为：SUCCESS_NO_INFO:-2; EXECUTE_FAILED:-3
            conn.commit();//执行完后，手动提交事务
            conn.setAutoCommit(true);//在把自动提交打开
        } catch (SQLException e) {
            log.error("语句执行出错！", e);
            return 0;
        } finally {
            closeAll(rs, stmt, conn);
        }
        return count;
    }

    /**
     * 写入一条记录并返回主键值
     *
     * @param sql
     * @return
     * @version 1.0 2016-5-17 SoChishun Added.
     * @see http://hqhou.blog.163.com/blog/static/85203935200981533715448/
     */
    public static int executeInsert(String sql) {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空，必须是insert语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        int id = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); // 执行并返回主键
            pstmt.executeUpdate(sql);
            rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1);
            }
        } catch (SQLException e) {
            log.error(sql + "语句执行出错！", e);
            return 0;
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return id;
    }

    /**
     * 传入的sql语句只能够是count(*) 或者sum(字段)或者max(字段)，只能够查询整数的sql。
     *
     * @param sql
     * @return 返回一个数字。0或者比0大的整数。
     */
    public static int getCount(String sql) {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空，必须是select语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        int result = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            log.error(sql + "语句查询出错！", e);
            return 0;
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return result;
    }

    /**
     * 传入的sql语句只能够获取一个字符串结果的sql，第一个字段必须是String类型，其他的会被忽略。<br>
     * 此方法只能用于返回要查询的结果既不为null也不是空字符串的情况。
     *
     * @param sql
     * @return 要么返回null，要么返回一个不是空字符串的结果。
     */
    public static String getStringResult(String sql) {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空，必须是select语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        String result = "";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                result = rs.getString(1);
            }
        } catch (SQLException e) {
            log.error(sql + "语句查询出错！", e);
            return null;
        } finally {
            closeAll(rs, pstmt, conn);
        }
        if ("".equals(result)) {
            result = null;
        }
        return result;
    }

    /**
     * 传入的sql语句只能够是count(*) 或者sum(字段)或者max(字段)，只能够查询整数的sql。
     *
     * @param sql
     * @return 返回一个float类型的数字
     */
    public static float getFloatCount(String sql) {
        if (sql == null || sql.trim().equals("")) {
            log.info("参数sql不能为空，必须是select语句。");
            throw new NullPointerException("sql参数不能为空字符串或者null。");
        }
        float result = 0.0f;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                result = rs.getFloat(1);
            }
        } catch (SQLException e) {
            log.error(sql + "语句查询出错！", e);
            return 0;
        } finally {
            closeAll(rs, pstmt, conn);
        }
        return result;
    }
}
